﻿using System;
using System.Collections.Generic;
using ExplorerPlus.DomainObjects;

namespace ExplorerPlus.PersistenceLayer.Sql
{
    public static class DataAccessFlyWeight
    {
        public static List<FlyWeightItem> GetAllCategories()
        {
            string sqlCommand = "Select CategoryId, CategoryName From Category " 
                + "Order By SortIndex";

            //ExplorerPlus.DbPersistenceLayer.

            return Common.GetFlyWeightItems(sqlCommand, "CategoryId", "CategoryName");
        }

        public static List<FlyWeightItem> GetCategoryFields(int categoryId)
        {
            string sqlCommand = "SELECT FieldId, FieldName FROM CategoryField "
                + "WHERE (CategoryId = " + categoryId.ToString() 
                + ") AND (IsNavigable = 1) ORDER BY FieldName";

            return Common.GetFlyWeightItems(sqlCommand, "FieldId", "FieldName");
        }

        public static List<FlyWeightItem> GetCategoryFieldItems(int categoryId, int fieldId)
        {
            string sqlCommand = "SELECT DISTINCT ItemPropertyValue, FieldId FROM ItemProperty "
                + "WHERE (CategoryId = " + categoryId.ToString()
                + ") AND (FieldId = " + fieldId.ToString()
                + ") ORDER BY ItemPropertyValue";

            return Common.GetFlyWeightItems(sqlCommand, "FieldId", "ItemPropertyValue");
        }

        public static List<FlyWeightItem> GetItems(int categoryId, int fieldId, string fieldValue)
        {
            string sqlCommand = "SELECT Item.ItemId, Item.ItemName FROM Item "
                + "INNER JOIN ItemProperty ON Item.CategoryId = ItemProperty.CategoryId AND Item.ItemId = ItemProperty.ItemId "
                + "WHERE (ItemProperty.FieldId = " + fieldId.ToString()
                + ") AND (ItemProperty.ItemPropertyValue LIKE N'" + fieldValue
                + "') AND (ItemProperty.CategoryId = " + categoryId.ToString()
                + ") ORDER BY Item.ItemName";

            return Common.GetFlyWeightItems(sqlCommand, "ItemId", "ItemName");
        }

    }
}
